import numpy as np
import random
import re
import sqlite3
from matplotlib import pyplot as plt


class StudentManagementSystem:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.create_tables()
        self.init_data()

    def create_tables(self):
        cursor = self.conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id TEXT UNIQUE,
                name TEXT,
                class_id TEXT,
                gender TEXT
            )
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS courses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                course_name TEXT UNIQUE
            )
        ''')
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS grades (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                student_id TEXT,
                course_id INTEGER,
                grade REAL,
                FOREIGN KEY (student_id) REFERENCES students(student_id),
                FOREIGN KEY (course_id) REFERENCES courses(id)
            )
        ''')
        self.conn.commit()

    def init_data(self):
        cursor = self.conn.cursor()
        cursor.execute("DELETE FROM students")
        cursor.execute("DELETE FROM courses")
        cursor.execute("DELETE FROM grades")

        courses = ['语文', '英语', '数学', '物理', '化学']
        class_ids = [f"2207{str(j).zfill(2)}" for j in range(10)]
        for course in courses:
            cursor.execute("INSERT INTO courses (course_name) VALUES (?)", (course,))
        self.conn.commit()

        # 插入50个学生和他们的成绩
        for i in range(50):
            student_id = f"202234{str(i + 1).zfill(6)}"
            name = f"学生{i + 1}"
            class_id = random.choice(class_ids)
            gender = random.choice(['男', '女'])
            cursor.execute("INSERT INTO students (student_id, name, class_id, gender) VALUES (?,?,?,?)",
                           (student_id, name, class_id, gender))
            for course in courses:
                cursor.execute("SELECT id FROM courses WHERE course_name =?", (course,))
                course_id = cursor.fetchone()[0]
                grade = round(random.uniform(0, 100), 2)
                cursor.execute("INSERT INTO grades (student_id, course_id, grade) VALUES (?,?,?)",
                               (student_id, course_id, grade))
        self.conn.commit()

    def add_student(self, student_id, name, class_id, gender):
        if not re.match(r'^\d{12}$', student_id):
            raise ValueError("学号必须是12位数字")
        if not (re.match(r'^[\u4e00-\u9fa5]+$', name) or re.match(r'^[a-zA-Z]+$', name)):
            raise ValueError("姓名只能包含全中文或全英文")
        if not re.match(r'^\d{6}$', class_id):
            raise ValueError("班级编号必须是6位数字")
        if gender not in ['男', '女']:
            raise ValueError("性别只能是男或女")

        cursor = self.conn.cursor()
        try:
            cursor.execute("SELECT student_id FROM students WHERE student_id =?", (student_id,))
            existing_student = cursor.fetchone()
            if existing_student:
                return False
            cursor.execute("INSERT INTO students (student_id, name, class_id, gender) VALUES (?,?,?,?)",
                           (student_id, name, class_id, gender))
            self.conn.commit()
            # 为新学生插入默认成绩 -1
            courses = ['语文', '英语', '数学', '物理', '化学']
            for course in courses:
                cursor.execute("SELECT id FROM courses WHERE course_name =?", (course,))
                course_id = cursor.fetchone()[0]
                cursor.execute("INSERT INTO grades (student_id, course_id, grade) VALUES (?,?,?)",
                               (student_id, course_id, -1))
            self.conn.commit()
            return True
        except sqlite3.IntegrityError:
            return False

    def add_grade(self, student_id, course_name, grade):
        try:
            grade = float(grade)
            if not (0 <= grade <= 100):
                raise ValueError("成绩必须在0到100之间")
        except ValueError:
            raise ValueError("成绩必须是一个可以转换为数字的值")

        if course_name not in ['语文', '英语', '数学', '物理', '化学']:
            raise ValueError("课程名称只能是预设的语文、英语、数学、物理、化学之一")

        cursor = self.conn.cursor()
        cursor.execute("SELECT id FROM courses WHERE course_name =?", (course_name,))
        course_id = cursor.fetchone()[0]
        try:
            cursor.execute("UPDATE grades SET grade = ? WHERE student_id = ? AND course_id = ?",
                           (grade, student_id, course_id))
            if cursor.rowcount == 0:
                cursor.execute("INSERT INTO grades (student_id, course_id, grade) VALUES (?,?,?)",
                               (student_id, course_id, grade))
            self.conn.commit()
            return True
        except sqlite3.IntegrityError:
            self.conn.rollback()  # 回滚事务
            return False

    def view_student_info(self, student_id=None):
        cursor = self.conn.cursor()
        if student_id:
            cursor.execute('''
                SELECT s.student_id, s.name, s.class_id, s.gender, c.course_name, g.grade
                FROM students s
                LEFT JOIN grades g ON s.student_id = g.student_id
                LEFT JOIN courses c ON g.course_id = c.id
                WHERE s.student_id =?
            ''', (student_id,))
        else:
            cursor.execute('''
                SELECT s.student_id, s.name, s.class_id, s.gender, c.course_name, g.grade
                FROM students s
                LEFT JOIN grades g ON s.student_id = g.student_id
                LEFT JOIN courses c ON g.course_id = c.id
            ''')
        return cursor.fetchall()

    def visualize_grade(self, student_id=None, class_id=None):
        cursor = self.conn.cursor()
        if student_id:
            cursor.execute('''
                SELECT c.course_name, g.grade
                FROM courses c
                JOIN grades g ON c.id = g.course_id
                WHERE g.student_id =?
            ''', (student_id,))
            data = cursor.fetchall()
            if data:
                courses = [row[0] for row in data]
                grades = [row[1] for row in data]
                fig, ax = plt.subplots()
                ax.bar(courses, grades)
                ax.set_title(f"学生 {student_id} 的成绩")
                ax.set_xlabel("课程")
                ax.set_ylabel("成绩")
                return fig
            else:
                return None
        elif class_id:
            cursor.execute('''
                SELECT c.course_name, AVG(g.grade)
                FROM courses c
                JOIN grades g ON c.id = g.course_id
                JOIN students s ON g.student_id = s.student_id
                WHERE s.class_id =?
                GROUP BY c.course_name
            ''', (class_id,))
            data = cursor.fetchall()
            if data:
                courses = [row[0] for row in data]
                avg_grades = [row[1] for row in data]
                fig, ax = plt.subplots()
                ax.bar(courses, avg_grades)
                ax.set_title(f"{class_id} 班级的平均成绩")
                ax.set_xlabel("课程")
                ax.set_ylabel("平均成绩")
                return fig
            else:
                return None
        else:
            return None

    def get_student_grade_table_by_id(self, student_id):
        """
        根据学号获取学生的成绩信息，以表格形式返回，包括课程、成绩和是否及格，并将及格和不及格分类
        """
        cursor = self.conn.cursor()
        cursor.execute('''
            SELECT c.course_name, g.grade
            FROM courses c
            JOIN grades g ON c.id = g.course_id
            WHERE g.student_id =?
        ''', (student_id,))
        grade_info = cursor.fetchall()
        passed_table = []
        failed_table = []
        for course, grade in grade_info:
            is_passed = "及格" if grade >= 60 else "不及格"
            if is_passed == "及格":
                passed_table.append((course, grade, is_passed))
            else:
                failed_table.append((course, grade, is_passed))
        return passed_table, failed_table

    def visualize_student_grade_classification(self, student_id):
        """
        可视化展示指定学号学生的成绩分类情况（及格与不及格）
        """
        passed_courses, failed_courses = self.get_student_grade_info_by_id(student_id)

        # 准备数据用于可视化
        passed_course_names = [course[0] for course in passed_courses]
        passed_grades = [course[1] for course in passed_courses]
        failed_course_names = [course[0] for course in failed_courses]
        failed_grades = [course[1] for course in failed_courses]

        fig, ax = plt.subplots()
        bar_width = 0.35
        bar_positions_passed = np.arange(len(passed_course_names))
        bar_positions_failed = bar_positions_passed + bar_width

        ax.bar(bar_positions_passed, passed_grades, width=bar_width, label='及格')
        ax.bar(bar_positions_failed, failed_grades, width=bar_width, label='不及格')

        ax.set_title(f"学生 {student_id} 成绩分类情况")
        ax.set_xlabel("课程")
        ax.set_ylabel("成绩")
        ax.set_xticks(bar_positions_passed + bar_width / 2)
        ax.set_xticklabels(passed_course_names + failed_course_names, rotation=45, ha='right')
        ax.legend()

        return fig

    def get_student_name_by_id(self, student_id):
            """
                   根据学号获取学生姓名
                   """

            cursor = self.conn.cursor()
            cursor.execute('''
                               SELECT name
                               FROM students
                               WHERE student_id =?
                               ''', (student_id,))
            result = cursor.fetchone()
            if result:
                return result[0]
            raise ValueError(f"找不到学号为 {student_id} 的学生")

